with customer_project as (--1客户表出主数据
    select t1.project_name
         , t1.customer_id
         , t1.customer_code
         , t1.customer_name
         , t1.customer_mall_id
         , t1.customer_mall_name
         , t1.customer_category
         , t1.customer_network_id
         , t1.customer_network_code
         , t1.customer_network_name
         , t1.order_id
         , t1.waybill_id
         , t1.terminal_dispatch_code
         , t1.order_status_code
         , t1.pick_time
         , t1.input_time
         , t1.is_transfer
         , t1.real_pick_network_code
         , t1.real_pick_network_name
         , t1.dispatch_network_code
         , t1.dispatch_network_name
         , t1.destination_id
         , t1.cancel_time
         , t1.sender_province_id
         , t1.sender_province_name
         , t1.sender_city_id
         , t1.sender_city_name
         , t1.sender_area_id
         , t1.sender_area_name
         , t1.receiver_province_id
         , t1.receiver_province_name
         , t1.receiver_city_id
         , t1.receiver_city_name
         , t1.receiver_area_id
         , t1.receiver_area_name
         , t1.order_source_code
         , t1.order_source_name
         , t1.way_input_time
         , t1.taking_early_scantime
         , t1.deliver_early_scantime
         , t1.outstore_early_scantime
         , t1.in_store_scan_time
         , t1.sign_early_scantime
         , t1.last_update_time
         , t1.taking_early_scansitecode
         , t1.deliver_early_scansitecode
         , t1.instore_early_scansitecode
         , t1.outstore_early_scansitecode
         , t1.sign_early_scansitecode
         , t1.is_intercept
         , t1.is_difficult
         , t1.is_sign
         , t1.work_order_join
         , t1.arbitration_join
         , t1.express_join
         , t1.xujia_sign_join
         , t1.problem_type_id
         , t1.problem_type_code
         , t1.problem_type_name
         , t1.problem_type_subject_id
         , t1.problem_type_subject_code
         , t1.problem_type_subject_name
         , t1.problem_description
         , t1.first_type_code
         , t1.first_type_name
         , t1.second_type_code
         , t1.second_type_name
         , t1.arb_first_type_code
         , t1.arb_first_type
         , t1.arb_second_type_code
         , t1.arb_second_type
         , t1.date_time
         , t1.work_type
         , t2.agent_code                                          as customer_agent_code
         , t2.agent_name                                          as customer_agent_name
         , t1.comp_create_time                                                  --客诉登记时间
         , t1.arb_create_time                                                   --仲裁申报时间
         , t1.problem_create_time                                               --质量申报时间
         , coalesce(t1.pick_time, t1.way_input_time)              as start_time --时效开始,coalesce(寄件,录入时间)
         , coalesce(sign_early_scantime, outstore_early_scantime) as sign_time
    from jms_dwd.dwd_wide_customer_order_waybill_dt t1
             left join jms_dim.dim_network_whole_massage t2
                       on t1.customer_network_code = t2.code
    where dt >= date_add('{{ execution_date | cst_ds }}', -44)
      and dt <= '{{ execution_date | cst_ds }}'
      and project_name is not null
      and customer_code is not null
      and customer_category = 6
),
     market_signtimely_pp as (--时效配置表
         select *
         from (
                  select *
                       , row_number()
                          over (partition by project_name,origin_provice_id,purpose_province_id order by update_time desc ) as rn
                  from jms_dim.dim_tab_dw_market_signtimely_base
                  where timely_type = '省份-省份'
              ) a
         where rn = 1
     ),
     market_signtimely_pc as (
         select *
         from (
                  select *
                       , row_number()
                          over (partition by project_name,origin_provice_id,purpose_province_id,purpose_city_id order by update_time desc ) as rn
                  from jms_dim.dim_tab_dw_market_signtimely_base
                  where timely_type = '省份-城市'
              ) a
         where rn = 1
     ),
     market_signtimely_cp as (
         select *
         from (
                  select *
                       , row_number()
                          over (partition by project_name,origin_provice_id,purpose_province_id,origin_city_name order by update_time desc ) as rn
                  from jms_dim.dim_tab_dw_market_signtimely_base
                  where timely_type = '城市-省份'
              ) a
         where rn = 1
     ),
     market_signtimely_cc as (
         select *
         from (
                  select *
                       , row_number()
                          over (partition by project_name,origin_provice_id,purpose_province_id,origin_city_name,purpose_city_id order by update_time desc ) as rn
                  from jms_dim.dim_tab_dw_market_signtimely_base
                  where timely_type = '城市-城市'
              ) a
         where rn = 1
     )
insert
overwrite
table
jms_dm.dm_mng_customer_daliy_waybill_detail_dt
partition
(
dt
)
select t1.project_name
     , t1.customer_id                                                          as id
     , t1.customer_code                                                        as code
     , t1.customer_name                                                        as name
     , t1.customer_name                                                        as network_id
     , t1.waybill_id                                                           as waybill_no
     , t1.order_status_code
     , t1.input_time
     , t1.pick_time
     , t1.real_pick_network_code
     , t1.dispatch_network_code
     , t1.destination_id
     , if(t1.order_status_code = 104, 1, 0)                                    as cancel_flag
     , if(t1.pick_time is not null, 1, 0)                                      as pick_flag
     , t1.date_time
     , t1.is_sign
     , t1.last_update_time
     , t1.way_input_time
     , if(t1.is_sign <> 0, 1, 0)                                               as sign_falg
     , if(
        datediff('{{ execution_date | cst_ds }}', substr(t1.last_update_time, 1, 10)) > 7, 1,
        0)                                                                     as 7d_update_flag
     , if(t1.way_input_time is not null and t1.order_status_code <> 104, 1, 0) as actual_taking_join
     , null                                                                    as del_war_flag
     , if(t1.deliver_early_scantime is not null, 1, 0)                         as del_war_join
     , null                                                                    as intercept_flag         --拦截件标识
     , t1.is_intercept                                                         as intercept_join
     , null                                                                    as difficult_flag
     , t1.is_difficult                                                         as difficute_join
     , t1.work_type                                                            as work_order_flag
     , t1.work_order_join
     , null                                                                    as arbitration_flag
     , t1.arbitration_join
     , null                                                                    as express_flag
     , t1.express_join
     , null                                                                    as xujia_sign_flag
     , t1.xujia_sign_join
     , t1.cancel_time
     , t1.order_id
     , t1.real_pick_network_name
     , coalesce(t1.sign_early_scansitecode, t1.outstore_early_scansitecode)    as sign_network_code
     , t6.name                                                                 as sign_network_name
     , t1.sign_time                                                            as sign_time
     , t1.problem_type_id
     , t1.problem_type_code
     , t1.problem_type_name
     , t1.problem_type_subject_id
     , t1.problem_type_subject_code
     , t1.problem_type_subject_name
     , t1.problem_description
     , t1.first_type_code
     , t1.first_type_name
     , t1.second_type_code
     , t1.second_type_name
     , t1.deliver_early_scantime                                               as dispatch_time
     , if(datediff('{{ execution_date | cst_ds }}', t1.input_time) >= 3, 1,
          0)                                                                   as diff_3input_time_flag  --1代表订单创建时间大于3天的标识
     , if(datediff('{{ execution_date | cst_ds }}', input_time) >= 10, 1,
          0)                                                                   as diff_10input_time_flag --1代表订单创建时间大于10天的标识
     , if(t1.project_name = '唯品会项目', 1, 0)                                     as wph_pro_name_flag      --1代表项目名是唯品会 0代表其他
     , if(t1.pick_time is not null, 1, 0)                                      as pick_time_flag         --1代表揽收时间不为null 0代表空
     , t1.sender_province_id                                                                             --寄件省份id
     , t1.sender_province_name                                                                           --寄件省份名称
     , t1.sender_city_id                                                                                 --寄件城市id
     , t1.sender_city_name                                                                               --寄件城市名
     , t1.sender_area_id                                                                                 --寄件区域Id
     , t1.sender_area_name                                                                               --寄件区域名称
     , t1.receiver_province_id                                                                           --收件省份id
     , t1.receiver_province_name                                                                         --收件省份名称
     , t1.receiver_city_id                                                                               --收件城市id
     , t1.receiver_city_name                                                                             --收件城市名称
     , t1.receiver_area_id                                                                               --收件区域id
     , t1.receiver_area_name                                                                             --收件区域名称
     , t1.in_store_scan_time                                                                             --入库扫描时间
     , t1.start_time                                                           as start_time             --时效开始,coalesce(寄件,录入时间)
     , t1.sign_time                                                            as end_time               --时效结束,coalesce(入库,签收时间)
     , case
           when t1.is_sign <> 0 then (unix_timestamp(sign_time) - unix_timestamp(start_time)) / (60 * 60 * 24)
           when t1.is_sign = 0 then
                   (unix_timestamp(concat('{{ execution_date | cst_ds }}', ' 23:59:59')) - unix_timestamp(start_time)) /
                   (60 * 60 * 24)
    end                                                                        as diff_day
     , t2.city_t_timely                                                        as pp_city_t_timely       --省省时效天数
     , t3.city_t_timely                                                        as pc_city_t_timely       --省市时效天数
     , t4.city_t_timely                                                        as cp_city_t_timely       --市省时效天数
     , t5.city_t_timely                                                        as cc_city_t_timely       --市市时效天数
     , greatest(t2.city_t_timely, t3.city_t_timely, t4.city_t_timely,
                t5.city_t_timely)                                              as need_city_t_timely     --时效天数
     , t1.dispatch_network_name                                                                          --派件网点name
     , t1.taking_early_scantime                                                                          --最早揽收时间
     , from_unixtime(unix_timestamp(taking_early_scantime) + 6 * 60 * 60,
                     'yyyy-MM-dd')                                             as taking_early_scanday   --最早揽收日期
     , if(unix_timestamp(t1.taking_early_scantime) - unix_timestamp(t1.input_time) <= 24 * 60 * 60, 1,
          0)                                                                   as on_taking_flag         --准点揽收标识,1代表24小时内揽收
     , t1.customer_mall_id                                                     as mall_id                --商家id（主要对接第三方客户）
     , t1.customer_mall_name                                                   as mall_name              --商家名称
     , t1.customer_network_id                                                                            --客户所属网点id
     , t1.customer_network_code                                                                          --客户所属网点code
     , t1.customer_network_name                                                                          --客户所属网点name
     , t1.arb_first_type_code
     , t1.arb_first_type
     , t1.arb_second_type_code
     , t1.arb_second_type
     , t1.customer_agent_code
     , t1.customer_agent_name
     , t1.comp_create_time                                                                               --客诉登记时间
     , t1.arb_create_time                                                                                --仲裁申报时间
     , t1.problem_create_time                                                                            --质量申报时间
     , from_unixtime(unix_timestamp(input_time) + 6 * 60 * 60,
                     'yyyy-MM-dd')                                             as input_time_6hour       --订单录入加6日期
     , substr(input_time, 1, 10)                                               as dt
from customer_project t1
         left join market_signtimely_pp t2
                   on t1.project_name = t2.project_name
                       and t1.sender_province_id = t2.origin_provice_id
                       and t1.receiver_province_id = t2.purpose_province_id
         left join market_signtimely_pc t3
                   on t1.project_name = t3.project_name
                       and t1.sender_province_id = t3.origin_provice_id
                       and t1.receiver_province_id = t3.purpose_province_id
                       and t1.receiver_city_id = t3.purpose_city_id
         left join market_signtimely_cp t4
                   on t1.project_name = t4.project_name
                       and t1.sender_province_id = t4.origin_provice_id
                       and t1.receiver_province_id = t4.purpose_province_id
                       and t1.sender_city_id = t4.origin_city_id
         left join market_signtimely_cc t5
                   on t1.project_name = t5.project_name
                       and t1.sender_province_id = t5.origin_provice_id
                       and t1.receiver_province_id = t5.purpose_province_id
                       and t1.receiver_city_id = t5.purpose_city_id
                       and t1.sender_city_id = t5.origin_city_id
         left join jms_dim.dim_network_whole_massage t6
                   on coalesce(t1.sign_early_scansitecode, t1.outstore_early_scansitecode) = t6.code
    distribute by dt
   , pmod(hash(waybill_no), 2)
;
